SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`tb_user`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`tb_user` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `username` VARCHAR(45) NOT NULL ,
  `password` VARCHAR(45) NOT NULL ,
  `forname` VARCHAR(45) NULL ,
  `lastname` VARCHAR(45) NULL ,
  `email` VARCHAR(60) NULL ,
  `credits` INT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
  UNIQUE INDEX `username_UNIQUE` (`username` ASC) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`tb_teams`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`tb_teams` (
  `idteam` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`idteam`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`tb_spiele`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`tb_spiele` (
  `idspiel` INT NOT NULL AUTO_INCREMENT ,
  `idteam1` INT NOT NULL ,
  `idteam2` INT NOT NULL ,
  `datum` DATE NOT NULL ,
  PRIMARY KEY (`idspiel`) ,
  INDEX `fk_tb_spiele_tb_teams1_idx` (`idteam1` ASC) ,
  INDEX `fk_tb_spiele_tb_teams2_idx` (`idteam2` ASC) ,
  CONSTRAINT `fk_tb_spiele_tb_teams1`
    FOREIGN KEY (`idteam1` )
    REFERENCES `mydb`.`tb_teams` (`idteam` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_tb_spiele_tb_teams2`
    FOREIGN KEY (`idteam2` )
    REFERENCES `mydb`.`tb_teams` (`idteam` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`tb_resultate`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`tb_resultate` (
  `idResultate` INT NOT NULL AUTO_INCREMENT ,
  `idspiel` INT NOT NULL ,
  `resultat1` DECIMAL(10,0) NOT NULL ,
  `resultat2` DECIMAL(10,0) NOT NULL ,
  PRIMARY KEY (`idResultate`) ,
  INDEX `fk_tb_resultate_tb_spiele1_idx` (`idspiel` ASC) ,
  CONSTRAINT `fk_tb_resultate_tb_spiele1`
    FOREIGN KEY (`idspiel` )
    REFERENCES `mydb`.`tb_spiele` (`idspiel` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`tb_points`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`tb_points` (
  `idcredits` INT NOT NULL AUTO_INCREMENT ,
  `user_id` INT NOT NULL ,
  `idspiel` INT NOT NULL ,
  `addpoints` INT NULL ,
  PRIMARY KEY (`idcredits`) ,
  INDEX `fk_tb_credits_tb_user1_idx` (`user_id` ASC) ,
  INDEX `fk_tb_credits_tb_spiele1_idx` (`idspiel` ASC) ,
  CONSTRAINT `fk_tb_credits_tb_user1`
    FOREIGN KEY (`user_id` )
    REFERENCES `mydb`.`tb_user` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_tb_credits_tb_spiele1`
    FOREIGN KEY (`idspiel` )
    REFERENCES `mydb`.`tb_spiele` (`idspiel` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`tb_tipp`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`tb_tipp` (
  `idtipp` INT NOT NULL AUTO_INCREMENT ,
  `userid` INT NOT NULL ,
  `idspiel` INT NOT NULL ,
  `result1` DECIMAL(10,0) NOT NULL ,
  `result2` DECIMAL(10,0) NOT NULL ,
  `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  PRIMARY KEY (`idtipp`) ,
  INDEX `fk_tb_tipp_tb_user_idx` (`userid` ASC) ,
  INDEX `fk_tb_tipp_tb_spiele1_idx` (`idspiel` ASC) ,
  CONSTRAINT `fk_tb_tipp_tb_user`
    FOREIGN KEY (`userid` )
    REFERENCES `mydb`.`tb_user` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_tb_tipp_tb_spiele1`
    FOREIGN KEY (`idspiel` )
    REFERENCES `mydb`.`tb_spiele` (`idspiel` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

USE `mydb` ;

-- -----------------------------------------------------
-- Placeholder table for view `mydb`.`view1`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`view1` (`id` INT);

-- -----------------------------------------------------
-- View `mydb`.`view1`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`view1`;
USE `mydb`;
;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
